package com.hongdee.insurance.car.service.impl;

import com.hongdee.base.car.entity.TBcCar;
import com.hongdee.base.car.entity.TBcCarTwo;
import com.hongdee.base.car.service.ICarService;
import com.hongdee.base.carconfigure.entity.TBcCarConfigure;
import com.hongdee.base.carconfigure.service.ICarConfigureService;
import com.hongdee.base.carmodel.entity.TBcCarModel;
import com.hongdee.base.carmodel.service.ICarModelService;
import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.car.entity.TCrmCar;
import com.hongdee.crm.car.service.ITCrmCarService;
import com.hongdee.insurance.car.dao.ITInsuranceCarDao;
import com.hongdee.insurance.car.entity.TInsuranceCar;
import com.hongdee.insurance.car.service.ITInsuranceCarService;
import com.hongdee.insurance.quotation.service.ITInsuranceTaskService;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.beans.PropertyDescriptor;
import java.util.*;

/**
 * 名  称:TInsuranceCarSerciceImpl
 * 类  型:class
 * 描  述:
 * 路  径:com.hongdee.insurance.car.service.impl
 * 作  者: 术洪超
 * 日  期:2017-04-25 10:11
 */
@Service
public class TInsuranceCarSerciceImpl implements ITInsuranceCarService {

    private String tabName = "T_INSURANCE_CAR";
    private String tabEntity = "tInsuranceCar";
    private String crmCar = "t_crm_car";

    @Autowired
    private ITInsuranceCarDao itInsuranceCarDao;
    @Autowired
    private ICarConfigureService carConfigureService;
    @Autowired
    private ICarModelService carModelService;
    @Autowired
    private ICarService carService;
    @Autowired
    private ITCrmCarService crmCarService;
    @Autowired
    private ITInsuranceTaskService insuranceTaskService;


    private enum CAR_CONF_TYPE{
        BRAND /*品牌*/,SERIES/*车系*/,MODEL/*车型*/,CONF/*配置*/
    }

    /*
    * 解决拼接sql语句时避免出现1=1，加快语句执行速度
    *
    * */
    private Boolean appendWhereIfNeed(StringBuffer sql, Boolean hasWhere) {

        if (hasWhere == false) {
            sql.append(" where ");
        } else {
            sql.append(" and ");
        }
        return true;
    }

    /**
     * @param uid   登录用户 ID
     * @param start 起始记录
     * @param limit 页面大小
     * @param data  参数数组
     * @return Map
     * @throws Exception
     * @desc 通过T_INSURANCE_CAR的表字段车牌号plateNum、车主insured 、车龄carAge、车辆型号 carModel分页查询保险车辆档案
     * @author 术洪超
     */

    @Override
    public Map page(Map data, String start, String limit, String uid) throws Exception {

        StringBuffer sb = new StringBuffer();

        List params = new ArrayList();
        sb.append("select c.id,c.insured,c.insured_address,c.insured_ID_number insuredIdNumber, c.car_model,c.car_type,c.contact_name," +
                " c.contact_phone,c.driver_license,c.email,c.engine_num,c.first_record_date,c.passenger_weight," +
                " c.plate_num,c.power,c.real_value,c.use_property,c.vin,c.post_code,c.company_id,c.car_age,c.insurance_company," +
                " c.insurance_end_date,c.car_property,c.sales_property,c.car_brand,c.car_configure,c.car_series," +
                " c.car_level, ceil(TIMESTAMPDIFF(DAY,c.first_record_Date, NOW())/365) age,c.label from t_insurance_car c ")
                .append("  ")
            .append("where c.company_id = ?");

        params.add(TokenUtil.getCompanyId(uid));

        //拼接sql
        //判断车牌号是否为空
        if (data.get("plateNum") != null && !data.get("plateNum").toString().equals("")) {
            sb.append(" and c.plate_num like ?");
            params.add("%"+data.get("plateNum")+"%");
        }
        //根据VIN码查询
        if(!"".equals(StringUtil.formatEmpty(data.get("vin")))){
            sb.append(" and c.vin like ? ");
            params.add("%"+StringUtil.formatEmpty(data.get("vin"))+"%");
        }
        //判断车主是否为空
        if (data.get("insured") != null && !data.get("insured").toString().equals("")) {
            sb.append(" and c.insured like ?");
            params.add("%"+data.get("insured")+"%");
        }
        //车龄参数
        Object carAge = data.get("carAge");
        if (null != carAge&&!carAge.toString().equals("")) {
            sb.append(" and ceil(TIMESTAMPDIFF(DAY,c.first_record_Date, NOW())/365)=? ");
            params.add(carAge.toString());
        }
        //判断车辆型号是否为空
        if (!"".equals(StringUtil.formatEmpty(data.get("carModel")))) {
            sb.append(" AND c.car_model like ? ");
            params.add("%"+StringUtil.formatEmpty(data.get("carModel"))+"%");
        }
        sb.append("order by c.create_time desc ");
        return this.itInsuranceCarDao.page(sb.toString(), params.toArray(), start, limit);
    }

    /**
     * @param data 要保存的车辆实体
     * @param uid  登录人的id
     * @return Map
     * @throws Exception
     * @desc 通过主键是否为空判断是添加或者是修改；
     * 添加时，通过车架号vin判断车辆是否已经存在,如果已存在，抛异常跳出；如果不存在，保存
     * @author 术洪超
     */

    @Override
    public TInsuranceCar saveOrUpdate(TInsuranceCar data, String uid) throws Exception {
        boolean insertFlag=false;
        //id为空时则为添加，根据传过来的车架号vin判断车辆是否已存在
        if (data.getVin() != null) {
            TInsuranceCar car=this.existCarByVin(data.getVin());
            if(car!=null){
                data.setId(car.getId());
            }else{
                this.addInsuranceCar(data.getId(),uid);
            }
        }
        data.setCompanyId(TokenUtil.getCompanyId(uid));
        if (data.getId() == null) {
            this.getCarConfig(data,true);
            data.setCreateUser(uid);
            data.setCreateTime(Calendar.getInstance().getTimeInMillis());
        } else {
            this.getCarConfig(data,true);
            //id不为空时则为修改，将修改后的数据写入表里
            data.setUpdateUser(uid);
            data.setUpdateTime(Calendar.getInstance().getTimeInMillis());
        }
        data.setFirstRecordDate(DateUtils.formatLongTimeByYYYYMMDD(DateUtils.
                dayToLongTime(data.getFirstRecordDate())));
        //区分编辑和修改
        this.itInsuranceCarDao.saveOrUpdate(data);
        //回写车辆Crm车型 车系 配置信息
        this.updInsuranceToCrmCarInfo(data);
        return data;
    }
    /**
     * 回写车辆Crm车型 车系 配置信息
     * @param data
     * @throws Exception
     */
    private void updInsuranceToCrmCarInfo(TInsuranceCar data) throws Exception {
        com.hongdee.crm.car.entity.TCrmCar crmCar=crmCarService.getInfoByVin(data.getVin(),data.getCompanyId());
        if(crmCar!=null){
            crmCar.setOwnerIdentity(data.getInsuredIdNumber());
            crmCar.setEmail(data.getEmail());
            crmCar.setAddress(data.getInsuredAddress());
            if(!"".equals(data.getCarSeries())) {
                crmCar.setSeries(data.getCarSeries());
            }
            if(!"".equals(data.getCarBrand())){
                crmCar.setBrand(data.getCarBrand());
            }
            if(!"".equals(data.getCarConfigure())) {
                crmCar.setConfigure(data.getCarConfigure());
            }
            if(!"".equals(data.getCarModel())) {
                crmCar.setModel(data.getCarModel());
            }
            crmCar.setBuyDate(DateUtils.dayToLongTime(data.getFirstRecordDate()));
            this.itInsuranceCarDao.saveOrUpdate(crmCar);
        }else{
            List<String> params=new ArrayList<>();
            String sql="insert into t_crm_car(id, create_time, create_user, owner_address,  brand, buy_date, company_id, configure, " +
                    "contact_address, contact_name, contact_phone,  contact_tel,owner_email, engine_num, from_company,  " +
                    "guarantee_start_date,  model, owner_identity,  owner_name, owner_phone,owner_tel, plate_num,series,vin)" +
                    "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            params.add(data.getId());
            params.add(String.valueOf(data.getCreateTime()));
            params.add(data.getCreateUser());
            params.add(data.getInsuredAddress());
            params.add(StringUtil.formatEmpty(data.getCarBrand()));
            params.add(String.valueOf(DateUtils.dayToLongTime(data.getFirstRecordDate())));
            params.add(data.getCompanyId());
            params.add(StringUtil.formatEmpty(data.getCarConfigure()));
            params.add(data.getInsuredAddress());
            params.add(data.getContactName());
            params.add(data.getContactPhone());
            params.add(data.getContactPhone());
            params.add(data.getEmail());
            params.add(data.getEngineNum());
            params.add("是");
            params.add(String.valueOf(DateUtils.dayToLongTime(data.getFirstRecordDate())));
            params.add(data.getCarModel());
            params.add(data.getInsuredIdNumber());
            params.add(data.getContactName());
            params.add(data.getContactPhone());
            params.add(data.getContactPhone());
            params.add(data.getPlateNum());
            params.add(StringUtil.formatEmpty(data.getCarSeries()));
            params.add(data.getVin());
            this.itInsuranceCarDao.executeSQL(sql,params.toArray());
        }
        if(!"".equals(data.getLabel())) {
            String sql = "update t_bc_car_series set car_type_code=?,label_number=? where brand_code=? and car_series_code=? ";
            this.itInsuranceCarDao.executeSQL(sql,data.getLabel(),data.getLabel(),data.getCarBrand(),data.getCarSeries());
        }
    }


    public static Map<String, Object> beanToMap(Object obj) {
        Map<String, Object> params = new HashMap<String, Object>(0);
        try {
            PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
            PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);
            for (int i = 0; i < descriptors.length; i++) {
                String name = descriptors[i].getName();
                if (!"class".equals(name)) {
                    params.put(name, propertyUtilsBean.getNestedProperty(obj, name));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return params;
    }

    /**
     * 新增保险档案
     * @param data
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public TInsuranceCar saveAdd(TInsuranceCar data, String uid) throws Exception {
        data.setCompanyId(TokenUtil.getCompanyId(uid));
        this.getCarConfig(data, true);
        data.setCreateUser(uid);
        data.setCreateTime(Calendar.getInstance().getTimeInMillis());
        TInsuranceCar car=this.existCarByVin(data.getVin());
        //录入车主档案信息
        Map<String,Object> carInfoMap=beanToMap(data);

        if(car!=null){
            String sql="update T_Insurance_Car set insured=?,insured_ID_number=?,insured_address=?,contact_phone=?,email=?,plate_num=?,vin=?," +
                    "first_record_date=?,car_brand=?,car_series=?,car_model=?,car_level=? where id=?";
            List<Object> params=new ArrayList<>();
            params.add(data.getInsured());
            params.add(data.getInsuredIdNumber());
            params.add(data.getInsuredAddress());
            params.add(data.getContactPhone());
            params.add(data.getEmail());
            params.add(data.getPlateNum());
            params.add(data.getVin());
            params.add(data.getFirstRecordDate());
            params.add(data.getCarBrand());
            params.add(data.getCarSeries());
            params.add(data.getCarModel());
            params.add(data.getCarLevel());
            params.add(car.getId());
            carInfoMap.put("id",car.getId());
            this.itInsuranceCarDao.executeSQL(sql,params.toArray());
        }else {
            //填写入行驶证车主
            data.setDriverLicense(data.getInsured());
            data.setContactName(data.getInsured());
            //区分编辑和修改
            this.itInsuranceCarDao.saveOrUpdate(data);
            carInfoMap.put("id",data.getId());
        }
        crmCarService.addCrmCar(carInfoMap,uid);
        return data;
    }




    /**
     * 设置品牌、车型、车系、配置信息 为佣金信息做准备
     * @param car
     * @throws Exception
     */
    private void getCarConfig(TInsuranceCar car,boolean upd) throws Exception {
        if(!"".equals(car.getCarLevel()) && car.getCarLevel()!=null) {
            String type = car.getCarLevel();
            String v = car.getCarValue();
            if(!"".equals(v) && v!=null) {
                //配置信息 需要将车型、车系、品牌补全
                if (type.equals(String.valueOf(CAR_CONF_TYPE.CONF.ordinal()))) {
                    TBcCarConfigure bcCarConfigure = this.carConfigureService.getInfo(v);
                    if(bcCarConfigure!=null) {
                        car.setCarConfigure(bcCarConfigure.getConfigureName());
                        this.setModel(car, bcCarConfigure.getModelId());
                    }
                }
                if (type.equals(String.valueOf(CAR_CONF_TYPE.MODEL.ordinal()))) {
                    if (upd) {
                        car.setCarConfigure("");
                    }
                    //获得车型信息
                    TBcCarModel bcCarModel = carModelService.getInfo(v);
                    if(bcCarModel!=null) {
                        car.setCarModel(bcCarModel.getModelName());
                        this.setSeries(car, bcCarModel.getSeriesId());
                    }
                }
                if (type.equals(String.valueOf(CAR_CONF_TYPE.SERIES.ordinal()))) {
                    if (upd) {
                        car.setCarConfigure("");
                        car.setCarModel("");
                    }
                    this.setSeriesByCode(car, v);
                }
            }
        }
    }

    /**
     * 设置车型
     * @param car
     * @param id
     * @throws Exception
     */
    private void setModel(TInsuranceCar car,String id) throws Exception {
        //获得车型信息
        TBcCarModel bcCarModel=carModelService.getInfo(id);
        car.setCarModel(bcCarModel.getModelName());
    }

    /**
     * 设置车系
     * @param car
     * @param id
     * @throws Exception
     */
    private void setSeries(TInsuranceCar car,String id) throws Exception {
        //获得车系信息
        TBcCar bcCar=carService.getInfo(id);
        car.setCarSeries(bcCar.getCarSeriesCode());
        //获得品牌信息
        car.setCarBrand(bcCar.getBrandCode());
    }

    /**
     * 根据code设置车系
     * @param car
     * @param code
     * @throws Exception
     */
    private void setSeriesByCode(TInsuranceCar car,String code) throws Exception {
        //获得车系信息
        TBcCar bcCar=carService.getInfo(code);
        if(bcCar!=null) {
            car.setCarSeries(bcCar.getCarSeriesCode());
            //获得品牌信息
            car.setCarBrand(bcCar.getBrandCode());
        }

    }

    /**
     * 根据VIN码获得车辆信息
     * @param vin
     * @return
     * @throws Exception
     */
    private TInsuranceCar existCarByVin(String vin) throws Exception {
        return this.itInsuranceCarDao.getCarInfoByVin(vin);
    }

    @Override
    public Map info(String key, String value, String uid,String companyId) throws Exception {

        StringBuffer sb = new StringBuffer();
        List<Object> params = new ArrayList();
        //获取公司信息
        if("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        //从客户档案内查询 '1' insurance 代表来源客户档案
        sb.append(" select * from (select car.id,insured,insured_address,insured_ID_number insuredIDNumber,car.car_model,car_type,contact_phone, " +
                " driver_license,email,engine_num,first_record_date,passenger_weight,plate_num,vin,power,real_value,use_property,post_code,'1' insurance, " +
                " ceil(TIMESTAMPDIFF(DAY,first_record_Date, NOW())/365) car_age,car_level,case when label is null then '' else label end label from " + tabName + " car " +
                " where company_id = ? ");
        params.add(companyId);
        this.infoTosql(value,key,sb,params);
        sb.append(" limit 1)t1 ");
        sb.append(" union all ");
        //连接车主档案查询 '0' insurance 代表来自车主档案 ，后续动作需要将车主档案添加至客户档案表
        sb.append(" select * from ( ");
        sb.append(" select id,owner_name as insured,owner_address insured_address,'' insuredIdNumber,model car_model,'' car_type,owner_tel contact_phone, " +
                " owner_name driver_license,owner_email email,engine_num,DATE_FORMAT(FROM_UNIXTIME(buy_date/1000),'%Y-%m-%d') first_record_date,'' passenger_weight,plate_num,vin,'' power, " +
                " '' real_value,'' use_property,'' post_code,'0' insurance,ceil(TIMESTAMPDIFF(DAY,FROM_UNIXTIME(buy_date/1000), NOW())/365) car_age,'' car_level,'' label  from t_crm_car where company_id=? ");
        params.add(companyId);
        this.infoTosql(value,key,sb,params);
        sb.append(" limit 1 ");
        sb.append(" )t ");
        List<Map<String, Object>> list=this.itInsuranceCarDao.list(sb.toString(),params.toArray());
        if (list.size()==0){
           throw new Exception("未查询到客户信息！");
        }else {
            Map map=list.get(0);
            if(StringUtil.formatEmpty(map.get("carLevel")).equals("")){
                map.put("carModel","");
            }
            String vin=StringUtil.formatEmpty(map.get("vin"));
            Map insuranceMap=insuranceTaskService.getLatelyInsurance(vin,uid);
            if(insuranceMap!=null){
                if(insuranceMap.size()>0){
                    Long endDate=Long.valueOf(StringUtil.formatEmpty(insuranceMap.get("orderEndDate")));
                    String endDateSt= DateUtils.formatLongTime(endDate,DateUtils.YYYYMMDD);
                    map.put("insuranceEndDate",endDateSt);
                    map.put("insuranceCompany",StringUtil.formatEmpty(insuranceMap.get("commercialCompany")));
                }else{
                    map.put("insuranceEndDate","");
                    map.put("insuranceCompany","");
                }
            }else{
                map.put("insuranceEndDate","");
                map.put("insuranceCompany","");
            }
            return map;
        }
    }

    /**
     * 拼凑查询sql
     * @param value
     * @param key
     * @param sb
     * @param params
     * @throws Exception
     */
    private void infoTosql(String value,String key,StringBuffer sb,List<Object> params) throws Exception {
        if (!value.isEmpty() && !key.isEmpty()) {
            switch (key) {
                //车牌号
                case "0":
                    sb.append(" and plate_num like ? ");
                    params.add("%" + value + "%");

                    break;
                //vin
                case "1": {
                    sb.append(" and vin like ?");
                    params.add("%" + value + "%");

                }
                break;
            }
        } else {
            throw new Exception("请选择查询类型并填写查询内容！");
        }
    }


    private boolean existVin(String vin) throws Exception {

        Boolean hasWhere = false;

        StringBuffer sb = new StringBuffer();
        sb.append("select * from " + tabName + " c ");

        List params = new ArrayList();
        if (!vin.isEmpty() && !vin.equals("")) {

            hasWhere = appendWhereIfNeed(sb, hasWhere);
            sb.append(" c.vin = ? ");
            params.add(vin);
        }

        Map map = this.itInsuranceCarDao.page(sb.toString(), params.toArray(), null, null);
        return Integer.valueOf(map.get("total").toString()) > 0;

    }

    /**
     * 获得车主档案信息
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public TInsuranceCar getCarInfo(String id) throws Exception {
        return this.itInsuranceCarDao.getCarInfo(id);
    }

    @Override
    public Map getCarInfo(String id,String vin,String uid)throws Exception{
        StringBuffer sb=new StringBuffer();
        List<String> params=new ArrayList<>();
        sb.append("select c.id,c.insured,c.insured_address,c.insured_ID_number insuredIdNumber," +
                " case WHEN c.car_model is null then c.car_series else c.car_model end carModel,c.car_type,c.contact_name," +
                " c.contact_phone,c.driver_license,c.email,c.engine_num,c.first_record_date,c.passenger_weight," +
                " c.plate_num,c.power,c.real_value,c.use_property,c.vin,c.post_code,c.company_id,c.car_age,c.insurance_company," +
                " c.insurance_end_date,c.car_property,c.sales_property,c.car_brand,c.car_configure,c.car_series," +
                " c.car_level,ceil(TIMESTAMPDIFF(DAY,c.first_record_Date, NOW())/365) age,c.label,c.source  from t_insurance_car  c ")
                .append(" ");

        if(!"".equals(vin)){
            sb.append("where c.vin = ?");
            params.add(vin);
        }else {
            sb.append("where c.id = ?");
            params.add(id);
        }
        sb.append(" union all ");
        sb.append(" select id, " +
                "    owner_name AS insured, " +
                "    owner_address insured_address, " +
                "    owner_identity insuredIdNumber, " +
                "    case WHEN model is null then series else model end  carModel, " +
                "    '' car_type, " +
                "    owner_name contact_name, " +
                "    owner_tel contact_phone, " +
                "    owner_name driver_license, " +
                "    owner_email email, " +
                "    engine_num, " +
                "    DATE_FORMAT( " +
                "      FROM_UNIXTIME(buy_date / 1000), " +
                "      '%Y-%m-%d' " +
                "    ) first_record_date, " +
                "    '' passenger_weight, " +
                "    plate_num, " +
                "    '' power, " +
                "    '' real_value, " +
                "    '' use_property, " +
                "      vin, " +
                "    '' post_code, " +
                "    company_id, " +
                "    ceil( " +
                "      TIMESTAMPDIFF( " +
                "        DAY, " +
                "        FROM_UNIXTIME(buy_date / 1000), " +
                "        NOW() " +
                "      ) / 365 " +
                "    ) car_age, " +
                "    '' insurance_company, " +
                "      '' insurance_end_date, " +
                "   '' car_property, " +
                "  '' sales_property, " +
                "  brand car_brand, " +
                "  configure car_configure, " +
                "  series car_series, " +
                "  '' car_level,ceil( " +
                "   TIMESTAMPDIFF( " +
                "    DAY, " +
                "    FROM_UNIXTIME(buy_date / 1000), " +
                "    NOW() " +
                "   ) / 365 " +
                "  ) age,'' label,'' source  from t_crm_car ");
        if(!"".equals(vin)){
            sb.append(" where vin=? ");
            params.add(vin);
        }else{
            sb.append(" where id=? ");
            params.add(id);
        }

        List<Map<String,Object>> list=this.itInsuranceCarDao.list(sb.toString(),params.toArray());
        if(list.size()>0){
            return list.get(0);
        }
        return null;
    }

    @Override
    public Map getCarInfoByVin(String id,String uid)throws Exception{
        StringBuffer sb=new StringBuffer();
        List<String> params=new ArrayList<>();
        String companyId=TokenUtil.getCompanyId(uid);
        sb.append("select c.id,c.insured,c.insured_address,c.insured_ID_number insuredIdNumber, c.car_model,c.car_type,c.contact_name," +
                " c.contact_phone,c.driver_license,c.email,c.engine_num,c.first_record_date,c.passenger_weight," +
                " c.plate_num,c.power,c.real_value,c.use_property,c.vin,c.post_code,c.company_id,c.car_age,c.insurance_company," +
                " c.insurance_end_date,c.car_property,c.sales_property,c.car_brand,c.car_configure,c.car_series," +
                " c.car_level,ceil(TIMESTAMPDIFF(DAY,c.first_record_Date, NOW())/365) age,c.source  from t_insurance_car  c ")
                .append(" ")
                .append("where c.vin = ? and c.company_id=?");
        params.add(id);
        params.add(companyId);
        sb.append(" union all ");
        sb.append(" select id, " +
                "    owner_name AS insured, " +
                "    owner_address insured_address, " +
                "    owner_identity insuredIdNumber, " +
                "    model car_model, " +
                "    '' car_type, " +
                "    owner_name contact_name, " +
                "    owner_tel contact_phone, " +
                "    owner_name driver_license, " +
                "    owner_email email, " +
                "    engine_num, " +
                "    DATE_FORMAT( " +
                "      FROM_UNIXTIME(buy_date / 1000), " +
                "      '%Y-%m-%d' " +
                "    ) first_record_date, " +
                "    '' passenger_weight, " +
                "    plate_num, " +
                "    '' power, " +
                "    '' real_value, " +
                "    '' use_property, " +
                "      vin, " +
                "    '' post_code, " +
                "    company_id, " +
                "    ceil( " +
                "      TIMESTAMPDIFF( " +
                "        DAY, " +
                "        FROM_UNIXTIME(buy_date / 1000), " +
                "        NOW() " +
                "      ) / 365 " +
                "    ) car_age, " +
                "    '' insurance_company, " +
                "      '' insurance_end_date, " +
                "   '' car_property, " +
                "  '' sales_property, " +
                "  brand car_brand, " +
                "  configure car_configure, " +
                "  series car_series, " +
                "  '' car_level,ceil( " +
                "   TIMESTAMPDIFF( " +
                "    DAY, " +
                "    FROM_UNIXTIME(buy_date / 1000), " +
                "    NOW() " +
                "   ) / 365 " +
                "  ) age,'' source  from t_crm_car where vin=?  and company_id=? ");
        params.add(id);
        params.add(companyId);
        List<Map<String,Object>> list=this.itInsuranceCarDao.list(sb.toString(),params.toArray());
        if(list.size()>0){
            return list.get(0);
        }
        return null;
    }


    /**
     * 保存并新增客户档案信息
     * @param car
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public TInsuranceCar saveByInsuranceTask(TInsuranceCar car,String uid) throws Exception {
        Map info=this.info("1",car.getVin(),uid,"");
        if("0".equals(StringUtil.formatEmpty(info.get("insurance")))){
            //来自客户档案
            this.addInsuranceCar(car.getId(),uid);
        }
        return this.saveOrUpdate(car,uid);
    }


    /**
     * 从CRM车主档案移至车主档案
     * @param carId
     * @param uid
     * @throws Exception
     */
    @Override
    public void addInsuranceCar(String carId,String uid) throws Exception {
        StringBuffer sql=new StringBuffer();
        sql.append("insert into T_INSURANCE_CAR (id,create_time,create_user,insured,insured_address,insured_ID_number,car_brand,car_series,car_model,car_configure,car_type,contact_phone,driver_license,email,engine_num,first_record_date,passenger_weight,plate_num,vin,power, " +
                "real_value,use_property,car_age,company_id,contact_name,saler)");
        sql.append("select id,'"+System.currentTimeMillis()+"','"+uid+"',owner_name as insured,owner_address insured_address,'' insured_ID_number,brand,series,model,configure,'' car_type,owner_tel contact_phone, " +
                " owner_name driver_license,owner_email email,engine_num,DATE_FORMAT(FROM_UNIXTIME(buy_date/1000),'%Y-%m-%d') first_record_date,'' passenger_weight,plate_num,vin,'' power, " +
                " '' real_value,'' use_property,ceil(TIMESTAMPDIFF(DAY,FROM_UNIXTIME(buy_date/1000), NOW())/365) car_age,'"+ TokenUtil.getCompanyId(uid)+"',owner_name,saler  ");
        sql.append("FROM t_crm_car car WHERE car.id =? and not EXISTS (select * from t_insurance_car where id =car.id ) ");
        this.itInsuranceCarDao.executeSQL(sql.toString(),carId);
    }
}
